package mwa.cs.cr.ctrl;

import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import javazoom.upload.MultipartFormDataRequest;
import javazoom.upload.UploadBean;
import javazoom.upload.UploadException;
import javazoom.upload.UploadFile;
import mwa.cs.dao.*;
import mwa.cs.database.Database;
import mwa.cs.model.*;
import mwa.cs.util.*;

import org.json.simple.JSONObject;

public class CS21002Srvl extends HttpServlet {
        private static final long serialVersionUID = 1L;
        private Database db ;
        public String newImage ;
        public String detailShow;
        public String topicShow;
        public String topicEdit;
        public String topicDelete;
        private String vaAgentBrowser;
        private String vaUrlAddress;
        private int vnSiteId;
        private int vnUserId;
    /**
     * @see HttpServlet#HttpServlet()
     */
    public CS21002Srvl() {
        super();
    }
    
    public void processRequest(HttpServletRequest request,
                        HttpServletResponse response) throws ServletException, IOException {
        // get site_id
        vnSiteId = Integer.parseInt(request.getSession().getAttribute("site_id").toString());
        vnUserId = Integer.parseInt(request.getSession().getAttribute("user_id").toString());
        request.setCharacterEncoding("UTF-8");
                response.setCharacterEncoding("UTF-8"); 
                response.setContentType("text/JSON");
                
        String vaProcessType = request.getParameter("process_type");

        MultipartFormDataRequest voMultipartForm = null;
                UploadBean voUpload = new UploadBean();
                try {
                        voMultipartForm = new MultipartFormDataRequest(request);
                        
                        voUpload.setFolderstore(request.getRealPath("/")+"assets/excel");
                        voUpload.setWhitelist("*.jpg,*.png,*.gif,*.txt,*.xls,*.xlsx");
                        voUpload.setOverwrite(true);
                        voUpload.store(voMultipartForm,"files");
                
                Hashtable voFileHashtable = voMultipartForm.getFiles();
                UploadFile voFileUpload = (UploadFile) voFileHashtable.get("files");
                String nameFile = voFileUpload.getFileName();
                
                File voTobeRenameFile = new File(request.getRealPath("/")+"assets/excel/"+nameFile);
                try{
                        this.db = new Database();
                        GenDocDAO voGenDocDAO = new GenDocDAO(db);
                        String vaNewExcel = "";
        
                        if (voTobeRenameFile.getPath().indexOf( "xlsx" )!=-1){
                                vaNewExcel = "NEWS"+voGenDocDAO.generatingNextExDoc()+".xlsx";
                        }else{
                                vaNewExcel = "NEWS"+voGenDocDAO.generatingNextExDoc()+".xls";
                        }
                        
                        String filePath = request.getRealPath("/")+"assets/excel/"+vaNewExcel;          
                        voTobeRenameFile.renameTo(new File(filePath));
        
                        Excel voExcel = new Excel();
                        ArrayList<HashMap> voDataList = new ArrayList<HashMap>();
                        
                        if (voTobeRenameFile.getPath().indexOf( "xlsx" )!=-1){
                                voDataList = voExcel.readXLSXFile(filePath);
                        }else{
                                voDataList = voExcel.readXLSFile(filePath);
                        }
                                
                            for(int i=1; i<voDataList.size(); i++){
                                addData(request, response, voDataList.get(i));
                            }
                }catch(Exception edb){
                        PrintWriter voOut = response.getWriter();
                        try {
                                JSONObject voObject = new JSONObject();
                                voObject.put("success", "");
                                voObject.put("error", "เกิดข้อผิดพลาด: " + edb.getMessage());
                                
                                voOut.print(voObject.toJSONString());
                        } finally {
                                voOut.close();
                        }
                        edb.printStackTrace();
                } finally{
                	db.close();
                }
                        
                
                } catch (UploadException e) {
                        PrintWriter voOut = response.getWriter();
                try {
                        JSONObject voObject = new JSONObject();
                        voObject.put("success", "");
                        voObject.put("error", "เกิดข้อผิดพลาด: " + e.getMessage());
                        
                        voOut.print(voObject.toJSONString());
                } finally {
                        voOut.close();
                }       
                        e.printStackTrace();
                }
    }
    
    protected void addData(HttpServletRequest request, HttpServletResponse response, HashMap<Object, Object> map) throws ServletException, IOException {            
        // get customer info
        String vaAccount = "";
        String vaZone = "0";
        String vaRoute = "0";
        String vaSequence = "0";
        String vaPName = map.get(0).toString().trim();
        String vaFName = map.get(1).toString().trim();
        String vaLName = map.get(2).toString().trim();
        String vaCardId = map.get(3).toString().trim();
        String vaTaxId = map.get(4).toString().trim();
        String vaTaxBranch = map.get(5).toString().trim();
        String vaHouseId = map.get(6).toString().trim();
        String vaHomeNumber = map.get(7).toString().trim();
        String vaAddress = map.get(8).toString().trim();
        String vaCityCode = map.get(9).toString().trim();
        String vaDistrictCode = map.get(10).toString().trim();
        String vaProvinceCode = map.get(11).toString().trim();
        String vaPostCode = map.get(12).toString().trim();
        String vaTelephone = map.get(13).toString().trim();
        String vaContactHouseNumber = map.get(14).toString().trim();
        String vaContactAddress = map.get(15).toString().trim();
        String vaContactCityCode = map.get(16).toString().trim();
        String vaContactDistrictCode = map.get(17).toString().trim();
        String vaContactProvinceCode = map.get(18).toString().trim();
        String vaContactPostCode = map.get(19).toString().trim();
        String vaContactTelephone = map.get(20).toString().trim();
        // get meter info       
        String vaCustomerType = map.get(21).toString().trim();
        String vaCustomerClass = map.get(22).toString().trim();
        String vaStatus = map.get(23).toString().trim();
        String vaAccountOld = map.get(25).toString().trim();
        String vaMeterSize = map.get(26).toString().trim();
        String vaMeterBrand = map.get(27).toString().trim();
        String vaMeterSerialNumber = map.get(28).toString().trim();
        String vaMeterNumber = map.get(31).toString().trim();
        String vaMeterInstallDate = map.get(29).toString().trim();
        String vaMeterReadDate = map.get(30).toString().trim();
        String vaAllowance = map.get(24).toString().trim();
        String vaDeptAmount = map.get(32).toString().trim();
        String vaGarbageCharge = map.get(33).toString().trim();
        String vaWasteWaterCharge = map.get(34).toString().trim();
        String vaOtherCharge = map.get(35).toString().trim();
        // message
        String successMessage = "";
        String errorMessage = "";
        boolean errorOccur = false;
        // equals "" คือมีการส่งตัวแปรมา 
        // equals null คือไม่มีส่งข้อมูลมา
        
        this.db = new Database();
        
        if (!vaMeterInstallDate.equals(""))  vaMeterInstallDate = JDate.saveDate(vaMeterInstallDate);
        if (!vaMeterReadDate.equals(""))  vaMeterReadDate = JDate.saveDate(vaMeterReadDate);
        
        // change String to int in dept_amount
        if (vaDeptAmount.equals("")) vaDeptAmount = "0";
        int vnDeptAmount = Integer.parseInt(vaDeptAmount);
        if (vaGarbageCharge.equals("")) vaGarbageCharge = "0";
        int vnGarbageCharge = Integer.parseInt(vaGarbageCharge);
        if (vaWasteWaterCharge.equals("")) vaWasteWaterCharge = "0";
        int vnWasteWaterCharge = Integer.parseInt(vaWasteWaterCharge);
        if (vaOtherCharge.equals("")) vaOtherCharge = "0";
        int vnOtherCharge = Integer.parseInt(vaOtherCharge);
        
        Customer voCustomer = new Customer(vnSiteId, vaAccount, vaAccountOld, vaStatus, vaCustomerType, 
                        vaCustomerClass, vaPName, vaFName, vaLName, vaCardId, vaTaxId, vaTaxBranch, vaHouseId, 
                        vaHomeNumber, vaAddress, vaCityCode, vaDistrictCode, vaProvinceCode, vaPostCode, vaTelephone, 
                        vaContactHouseNumber, vaContactAddress, vaContactCityCode, vaContactDistrictCode, vaContactProvinceCode, 
                        vaContactPostCode, vaContactTelephone, vaMeterSize, vaMeterBrand, vaMeterSerialNumber, vaMeterNumber, 
                        vaMeterInstallDate, vaMeterReadDate, vaAllowance, vnDeptAmount, vnGarbageCharge, vnWasteWaterCharge, vnOtherCharge, 
                        0, null, null, 0, null, null);
        
        RouteDetail voRouteDetail = new RouteDetail(vnSiteId, Integer.parseInt(vaZone), Integer.parseInt(vaRoute), 
                        Integer.parseInt(vaSequence), vaAccount, vaMeterReadDate, 0, null, null, 0, null, null);
        
        CustMonthly voCustMonthly = new CustMonthly(vnSiteId, Integer.parseInt(vaZone), Integer.parseInt(vaRoute), 
                        vaAccount, JDate.getPreviousMonth(JDate.getMonth(), JDate.getYear()), 
                        JDate.getYearOfPreviousMonth(JDate.getMonth(), JDate.getYear()), 
                        Integer.parseInt(vaSequence), vaCustomerClass, null, 
                        vaMeterSerialNumber, vaMeterNumber, vaMeterReadDate, 0, 0, vaAllowance, 0, 0, 0, 0, null, vaStatus, 
                        0, null, null, 0, null, null);

                CustomerDAO voCustomerManagement = new CustomerDAO(db);
                RouteDetailDAO voRouteDetailManagement = new RouteDetailDAO(db);
                CustMonthlyDAO voCustMonthlyManagement = new CustMonthlyDAO(db);
                // check setting month and year in cust_monthly (period_month, period_year)
                String vaMaxMonthCustMonthly = null, vaMaxYearCustMonthly = null;
                vaMaxMonthCustMonthly = voCustMonthlyManagement.findMaxMonth(vnSiteId);
                vaMaxYearCustMonthly = voCustMonthlyManagement.findMaxYear(vnSiteId);
                // check account exist or not if exist, updating date else inserting data
                if (vaAccount.equals("")) {
                        if (!vaMaxMonthCustMonthly.equals("00")) {
                                voCustMonthly.setVaPeriodMonth(vaMaxMonthCustMonthly);
                                voCustMonthly.setVaPeriodYear(vaMaxYearCustMonthly);
                        }
                        db.beginTransaction();
                        // generate account
                        GenDocDAO voGenDoc = new GenDocDAO(db);
                        vaAccount = voGenDoc.generatingNextCRDoc(vnSiteId, JDate.getMonth(), JDate.getYear());
                        // set account, meter_date, insert_user_id, insert_date, insert_time
                        voCustomer.setVaAccount(vaAccount);
                        voCustomer.setVaMeterDate(vaMeterInstallDate);
                        voCustomer.setVnInsertUserId(vnUserId);
                        voCustomer.setVaInsertDate(JDate.getDate());
                        voCustomer.setVaInsertTime(JDate.getTime());
                        voRouteDetail.setVaAccount(vaAccount);
                        voRouteDetail.setVnInsertUserId(vnUserId);
                        voRouteDetail.setVaInsertDate(JDate.getDate());
                        voRouteDetail.setVaInsertTime(JDate.getTime());
                        voCustMonthly.setVaAccount(vaAccount);
                        voCustMonthly.setVnInsertUserId(vnUserId);
                        voCustMonthly.setVaInsertDate(JDate.getDate());
                        voCustMonthly.setvaInsertTime(JDate.getTime());
                        try {
                                voCustomerManagement.addCustomer(voCustomer);
                                voRouteDetailManagement.addRouteDetail(voRouteDetail);
                                voCustMonthlyManagement.insertCustMonthly(voCustMonthly);
                                db.commit();
                                successMessage = "บันทึกข้อมูลเรียบร้อยแล้ว";
                        } catch (Exception e) {
                                // errorOccur = true สำหรับเงื่อนไขที่ set Account = "" เวลาผู้ใช้กด save อีกรอบจะได้ไม่เข้าเงื่อนไขอัพเดท
                                errorOccur = true;
                                try {
                                        db.rollback();                                          
                                        //SMErrorLogDAO.addLog(0, "CS21002", JDate.getDate(), JDate.getTime(),vnUserId, "InsertCustomerSrvl", e.getMessage(),vaUrlAddress, vaAgentBrowser);                                     
                                        errorMessage = "เกิดข้อผิดพลาดในการเพิ่มข้อมูล กรุณาติดต่อผู้ดูแลระบบ";
                                } catch (Exception ex) {
                                        errorMessage = "เกิดข้อผิดพลาด: " + ex.getMessage();
                                }
                        }
                } 

                // if error set account ให้เป็นค่าเริ่มต้น
                if (errorOccur) voCustomer.setVaAccount("");    
                if (!vaMeterInstallDate.equals(""))  voCustomer.setVaMeterInstallDate(JDate.showDate(vaMeterInstallDate));
                if (!vaMeterReadDate.equals(""))  voRouteDetail.setVaMeterReadDate(JDate.showDate(vaMeterReadDate));

                PrintWriter out = response.getWriter();
                try {
                        JSONObject voObject = new JSONObject();
                        voObject.put("success", successMessage.trim());
                        voObject.put("error", errorMessage.trim());
                        
                        out.print(voObject.toJSONString());
                } finally {
                        db.close();
                        out.close();
                }       
        }
    

        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
                processRequest(request,response);
                
        }

        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
                processRequest(request,response);
        }

}
